
/**
	AUTHOR		: VEERU
	DATE		: JUNE 6th 2005
	DESCRIPTION	: THIS PROCEDURE IS USED TO MAP THE FINISHED GOOD WITH BLANK FGI.	
*/

ALTER PROC SP_MAP_FINISHEDGOOD_WITH_BLANKFGI

@PART_NUMBER VARCHAR( 50 ),
@OPN VARCHAR( 50 ),
@CUSTOMER_NAME VARCHAR( 50 ),
@P_CRC_CODE    VARCHAR( 50 ),
@P_ALGORITHM   VARCHAR( 50 ),
@P_PROGRAMCODE VARCHAR( 50 ),
@ERROR_CODE VARCHAR( 100 ) OUT

AS
BEGIN
	DECLARE @FG_PROCESSSTEP_ID    INT
	DECLARE @OPN_PROCESSSTEP_ID   INT
	DECLARE @DB_PARENT_PRODUCT_ID INT		
	DECLARE @DB_CUSTOMER_ID INT
	DECLARE @CUSTOMER_MAPPING_EXISTS INT

	--Getting the Finished Good Part Number's ProcessStep Id
	SELECT @FG_PROCESSSTEP_ID = P.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE = 'FG' AND PART_NUMBER = @PART_NUMBER	
	
	--Getting the Black FGI Part Number's ProcessStep Id
	SELECT @OPN_PROCESSSTEP_ID = P.PROCESSSTEP_ID, @DB_PARENT_PRODUCT_ID = P_PARENT_PRODUCT_ID  FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE = 'Blank FGI' AND PART_NUMBER = @OPN	

	--Checking Whether Mapping exists for the OPN and Finished Good, if not mapping them.
	IF ( ( SELECT COUNT(*) FROM FG_TO_FGI_MAP_TABLE WHERE FG_PROCESS_STEP_ID = @FG_PROCESSSTEP_ID AND FGI_PROCESS_STEP_ID = @OPN_PROCESSSTEP_ID AND BASE_PART_PRODUCT_ID = @DB_PARENT_PRODUCT_ID ) = 0 )
	BEGIN		

		INSERT INTO FG_TO_FGI_MAP_TABLE( FG_PROCESS_STEP_ID, FGI_PROCESS_STEP_ID, BASE_PART_PRODUCT_ID ) VALUES
       					( @FG_PROCESSSTEP_ID, @OPN_PROCESSSTEP_ID, @DB_PARENT_PRODUCT_ID )
		SET @ERROR_CODE = 'Success Mapping'	
	END
	ELSE
	BEGIN		
		SET @ERROR_CODE = 'Mapping Exists'	
	END

	-- Updating the Customer ID if the Customer ID is Null
	SELECT @DB_CUSTOMER_ID = CUSTOMER_ID FROM CUSTOMER WHERE C_NAME = @CUSTOMER_NAME
	
	SELECT @CUSTOMER_MAPPING_EXISTS = Customer_Id FROM FG_CUSTOMER_MAP_TABLE WHERE ProcessStep_ID = @FG_PROCESSSTEP_ID AND Customer_Id = @DB_CUSTOMER_ID
	
	IF( @CUSTOMER_MAPPING_EXISTS IS NULL OR @CUSTOMER_MAPPING_EXISTS = 0 )
	BEGIN
		-- Getting the Customer Id for the Product 
		INSERT INTO FG_CUSTOMER_MAP_TABLE ( ProcessStep_ID, Customer_Id ) VALUES ( @FG_PROCESSSTEP_ID, @DB_CUSTOMER_ID )
	END

	-- Updating the CRC code and Program Algorithm if they are not empty for the Finished Good Part Number.
	IF ( @P_CRC_CODE IS NOT NULL AND LEN(LTRIM(RTRIM(@P_CRC_CODE))) > 0 )
	BEGIN
		UPDATE PRODUCT SET P_CRC_CODE = @P_CRC_CODE WHERE PART_NUMBER = @PART_NUMBER	
	END

	IF ( @P_ALGORITHM IS NOT NULL AND LEN(LTRIM(RTRIM( @P_ALGORITHM ) ) )> 0 )
	BEGIN
		UPDATE PRODUCT SET P_ALGORITHM = @P_ALGORITHM WHERE PART_NUMBER = @PART_NUMBER	
		--If Algorithm is not in the System, Placing it in the System.
		IF ( ( SELECT COUNT(*) FROM FINISHED_GOODS_ALGORITHM_TABLE WHERE ALGORITHM_NAME = @P_ALGORITHM ) = 0 ) 
		BEGIN
			INSERT INTO FINISHED_GOODS_ALGORITHM_TABLE( ALGORITHM_NAME ) VALUES( @P_ALGORITHM )
		END
	END	
END

